package com.sam.query.mould.simple.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.sam.core.dao.jdbc.BaseJdbcTemplate;
import com.sam.core.util.BeanUtils;
import com.sam.core.util.DateUtils;
import com.sam.core.util.StringUtils;
import com.sam.query.mould.simple.dao.MouldSimpleDao;
import com.sam.query.mould.simple.domain.Config;
import com.sam.query.mould.simple.domain.DesignProcess;
import com.sam.query.mould.simple.domain.Field;
import com.sam.query.mould.simple.domain.Form;
import com.sam.query.mould.simple.domain.Mould;
import com.sam.query.mould.simple.domain.MouldClass;
import com.sam.query.mould.simple.domain.Operation;
import com.sam.query.mould.simple.domain.Part;
import com.sam.query.mould.simple.domain.Purchase;
import com.sam.query.mould.simple.domain.TransferPartMonitor;

/**
 * @project mould
 * @author Sam
 * @version
 * @date Nov 10, 2009 10:24:40 AM
 */
@Repository
@SuppressWarnings("unchecked")
public class MouldSimpleDaoImpl extends BaseJdbcTemplate implements MouldSimpleDao {

	public static void main(String[] args) {
		List<Mould> list = new ArrayList<Mould>();
		Mould mould = new Mould();
		mould.setMouldID("test");
		list.add(mould);
		list.remove(mould);
		System.out.println(list.size());
	}

	/*
	 * (non-Javadoc)
	 * @see com.sam.query.mould.simple.dao.MouldSimpleDao#queryMould(com.sam.query.mould.simple.domain.Form)
	 */
	public Integer queryMould(final Form mouldForm) {

		if (mouldForm.getMouldTextId() == null || mouldForm.getMouldTextId().equals("all")) {
			mouldForm.setMouldTextId("");
		}
		if (StringUtils.isEmpty(mouldForm.getMouldName())
				&& StringUtils.isEmpty(mouldForm.getMouldInteriorID())
				&& StringUtils.isEmpty(mouldForm.getPartMonitorID())) {
			return 1024;
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT DISTINCT MD.mouldID FROM Mould MD WITH (NOLOCK) "
				+ " INNER JOIN MouldPartTechnics MPT WITH (NOLOCK) ON MD.mouldID=MPT.mouldID "
				+ " WHERE MD.mark='applying' AND MPT.mark='applying' ");
		if (!StringUtils.isEmpty(mouldForm.getMouldName())) {
			sql.append(" AND MD.mouldName LIKE '%" + mouldForm.getMouldName() + "%' ");
		}
		if (!StringUtils.isEmpty(mouldForm.getMouldTextId())) {
			sql.append(" AND MD.mouldID LIKE '" + mouldForm.getMouldTextId() + "%'");
		}
		if (!StringUtils.isEmpty(mouldForm.getMouldInteriorID())) {
			sql.append(" AND MD.mouldInteriorID LIKE '" + mouldForm.getMouldInteriorID() + "%' ");
		}
		if (!StringUtils.isEmpty(mouldForm.getPartMonitorID())) {
			sql.append(" AND MPT.partMonitorID = " + mouldForm.getPartMonitorID());
		}
		final List<String> list = new ArrayList<String>();
		jdbcTemplate.query(sql.toString(), new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException {
				while (rs.next()) {
					list.add(rs.getString("mouldID"));
				}
				return null;
			}
		});
		int row = 0;
		if (list != null) {
			row = list.size();
			if (list.size() == 1) {
				mouldForm.setMouldTextId(list.get(0));
			}
		}
		return row;
	}

	/**
	 * 模具进度信息查询主方法
	 */
	public List<Mould> queryMouldState(final Config config, final Form form) throws Exception {
		StringBuffer sql = new StringBuffer(1024);
		sql
				.append(" SELECT DISTINCT PartClass.partClassName, M.mouldID,M.mouldName,M.mouldInteriorID,M.mouldClientID,M.ScheduleTerm, "
						+ " M.leadTime,M.canExterior, t_SJGL_AttachmentFile.fileName ,InStoreHistoryTable.maxStoreTime,"
						+ " M.mouldWorkingState,M.productName,M.productInteriorID,M.productClientID,M.projectName,"
						+ " M.projectInteriorID,M.projectClientID,M.MClass,M.MBClass, M.produID, M.projID ,"
						+ " CASE WHEN M.ScheduleTerm IS NOT NULL AND DATEDIFF(dd,M.ScheduleTerm,getDate())>0 THEN DATEDIFF(dd,M.ScheduleTerm,getDate()) "
						+ "      ELSE 0 END AS deferDay,"
						+ " MP.partID,MP.partName,MP.verifyID,ISNULL(AI.workID,'001') AS workID, MPT.partMonitorID,MPT.ID, "
						+ " Material.materialName,MP.remark,MP.specifications,MP.standardNumber,MP.machiningAttribute,MP.designDate,"
						+ " MP.partNums,MP.designer,MP.partType,MP.remark,MP.state MSTATE,MP.partOrderId, "
						+ " MPT.operationName,MPT.operationOrderID, ISNULL(MPT.operationContent,'') AS operationContent, MPT.planStartTime, MPT.planStopTime, "
						+ " MPT.actualStartTime,MPT.actualStopTime,CAST(ISNULL(MPT.processingHoursQuota,'0.5') AS FLOAT) AS processingHoursQuota,MPT.processingHoursActual,MPT.actualOperatorID, "
						+ " ISNull(RE1.resourceName,'') AS planResource,ISNULL(RE2.resourceName,'') AS actualResource,MPT.state,MPT.keyOp, "
						+ " MPT.planExterior, OPN.type, QRR.isEligible, QRR.isDealed "
						+ " FROM ( select mouldID,mouldName,mouldInteriorID,mouldClientID,ScheduleTerm,leadTime,canExterior, "
						+ " mouldWorkingState, productName,productInteriorID,productClientID,Mould.productID produID,Mould.projectID projID ,"
						+ " projectName,projectInteriorID,projectClientID,MouldClass.mouldClassName MClass,MC.mouldClassName MBClass "
						+ // 添加一些字段：模具进度简单中的模具信息配置
						" from Mould WITH (NOLOCK) inner join Product WITH (NOLOCK) on Mould.productID = Product.productID "
						+ " INNER JOIN Project ON Mould.projectID = Project.projectID "
						+ " INNER JOIN MouldClass ON Mould.mouldClassID = MouldClass.mouldClassID  "
						+ // 模具类型
						"  INNER JOIN MouldClass MC ON Mould.mouldBasicClassID = MC.mouldClassID "
						+ // 模具基本类型
						" where Mould.mark = 'applying' and Product.mark = 'applying' "
						+ " and mouldName like ? and mouldInteriorID like ? "); // param
		// #mouldName,#mouldInteriorID
		// :to
		// be
		// set
		if (form.getMouldClassID() != null && !form.getMouldClassID().equals("mo_01")) {
			sql.append(" and Mould.mouldClassID like '" + form.getMouldClassID() + "%' ");
		}
		if (form.getMouldTextId() != null && !form.getMouldTextId().equals("")) {
			sql.append(" and mouldID like '%" + form.getMouldTextId() + "%' ");
		}
		if (form.getMouldInteriorID() != null && !form.getMouldInteriorID().equals("")) {
			sql.append(" and mouldInteriorID like '%" + form.getMouldInteriorID() + "%' ");
		}
		if (form.getMouldClientIDName() != null && !form.getMouldClientIDName().equals("")) {
			sql.append(" and mouldClientID like '" + form.getMouldClientIDName() + "%' ");
		}
		if (form.getMouldWorkingState() != 0) {
			sql.append(" and mouldWorkingState = " + form.getMouldWorkingState());
		}
		if (!StringUtils.isEmpty(form.getQueryStartDate())
				&& !StringUtils.isEmpty(form.getQueryEndDate())) {
			if (form.getTimeSelect().equals("0")) {
				sql.append(" and Product.leadTime Between '" + form.getQueryStartDate() + "' AND '"
						+ DateUtils.getDefinateTime(form.getQueryEndDate(), false) + "' ");
			} else {
				sql.append(" and Mould.ScheduleTerm Between '" + form.getQueryStartDate()
						+ "' AND '" + DateUtils.getDefinateTime(form.getQueryEndDate(), false)
						+ "' ");
			}
		}
		sql
				.append(" ) M "
						+ " INNER JOIN MouldPart MP WITH (NOLOCK) ON MP.mouldID = M.mouldID "
						+ " left join Material  on Material.materialID =MP.materialID  "
						+ // just to query part's material
						" LEFT JOIN PartClass ON PartClass.partClassID = MP.partClassID "
						+ " LEFT JOIN MouldPartTechnics MPT WITH (NOLOCK) ON MPT.partID = MP.partID "
						+ " INNER JOIN OperationName OPN WITH (NOLOCK) on OPN.operationNameID = MPT.operationNameID "
						+ " LEFT JOIN QualityResultRecord QRR WITH (NOLOCK) ON QRR.checkOperationID=MPT.ID "
						+ " LEFT JOIN t_GYGH_AlterationOfPart AP WITH (NOLOCK) ON MP.partID=AP.partID "
						+ " LEFT JOIN AlterationInfo AI WITH (NOLOCK) ON AP.alterationID = AI.ID "
						+ " LEFT JOIN Resource RE1 WITH (NOLOCK) ON RE1.resourceID = MPT.planResourceID "
						+ " LEFT JOIN Resource RE2 WITH (NOLOCK) ON RE2.resourceID = MPT.actualResourceID "
						+ " left join t_SJGL_AttachmentFile ON  MPT.associatedID = t_SJGL_AttachmentFile.associatedID "
						+ // 是否存在上传的文档
						" left join (SELECT MAX(inStoreTime) maxStoreTime, mouldID FROM InStoreHistoryOfProduction GROUP BY mouldID )  "
						+ // 模具入库时间
						" InStoreHistoryTable  on InStoreHistoryTable.mouldID=MP.mouldID "
						+ " WHERE MP.mark='applying' ");
		if (!form.getMouldTextId().equals("")) {
			sql.append(" AND MPT.mouldID LIKE '" + form.getMouldTextId() + "%' ");
		}
		// 配置的相关信息
		String partOrder = config.getPartOrder();
		String partOrderDirection = config.getPartOrderDirection();
		final boolean isByOperationOrderID = config.isIsByOperationOrderID();
		String delayBenchmark = config.getDelayBenchmark();
		if (partOrder.equals("partMonitorID")) { // 工件的监控编号
			sql.append(" ORDER BY M.mouldID,MPT.partMonitorID " + partOrderDirection
					+ " , MPT.operationOrderID,MPT.ID, "
					+ " MPT.actualStopTime,MPT.actualStartTime ");

		} else if (partOrder.equals("verifyID")) { // 如果按照件号排序
			sql.append(" ORDER BY M.mouldID,MP.verifyID  " + partOrderDirection
					+ " ,MP.partID,MPT.operationOrderID,MPT.ID, "
					+ " MPT.actualStopTime,MPT.actualStartTime ");

		} else if (partOrder.equals("partName")) {
			sql.append(" ORDER BY M.mouldID,MP.partID,MP.partName " + partOrderDirection
					+ " ,MPT.operationOrderID,MPT.ID, "
					+ " MPT.actualStopTime,MPT.actualStartTime ");

		}

		Map returnMap = new HashMap();
		final List<Mould> moulds = new ArrayList<Mould>();
		final Map<Long, Operation> operationMap = new HashMap<Long, Operation>();
		final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Integer maxOperationNums = (Integer) jdbcTemplate.query(sql.toString(), new Object[] {
				"%" + StringUtils.formatNull(form.getMouldName()) + "%",
				"%" + StringUtils.formatNull(form.getMouldInteriorIDName()) + "%" },
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException {
						Map<String, String> humans = queryHumans();
						long start = System.currentTimeMillis();
						Map<String, Map> mouldProcesses = queryMouldProcessPercent(form);
						log.info("@#$查询模具及工序类型进度历时： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map partProcesses = queryPartProcessPercent(form);
						log.info("@#$查询工件进度历时： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map monitors = queryMonitors(form);
						log.info("@#$查询工序实际工时历时： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map<Long, List<Operation>> preMap = queryPreOperation(form);
						log.info("@#$查询工序间前置任务关系： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map<String, String> taskProcess = queryDesignProcess(form);
						Map<String, List<DesignProcess>> taskList = queryDesignTask(form);
						log.info("@#$该模具的设计任务： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map<String, String> cncProcess = queryCNCProcess(form);
						Map<String, List<DesignProcess>> cncList = queryCNCTask(form);
						log.info("@#$该模具的cnc任务： " + (System.currentTimeMillis() - start));
						start = System.currentTimeMillis();
						Map<String, Purchase> puMap = queryPurchase(form);
						log.info("@#$该模具的采购信息： " + (System.currentTimeMillis() - start));

						start = System.currentTimeMillis();
						Mould mould = null;
						ArrayList defaultList = new ArrayList();
						ArrayList overList = null;
						ArrayList workingList = null;
						ArrayList pauseList = null;
						ArrayList undoList = null;
						Part part = null;
						Operation operation = null;
						String mouldID = null;
						String partID = null;
						int maxOperationNums = 0;

						// kukuxia 2007-07-09 取得所有流转工序的最新纪录
						ArrayList resultList = new ArrayList();
						HashMap transferPartMonitorMapByOperation = new HashMap();
						HashMap transferPartMonitorMapByPart = new HashMap();
						resultList = queryTransferPartMonitorInfo();
						transferPartMonitorMapByOperation = (HashMap) resultList.get(0); // key
						// 为operationID的工件流转信息Map
						transferPartMonitorMapByPart = (HashMap) resultList.get(1); // key
						// 为partMonitorID的工件流转信息Map
						Map<String, Mould> mouldMap = new HashMap<String, Mould>();

						while (rs.next()) {
							if (!mouldProcesses.containsKey(rs.getString("mouldID"))) {
								continue;
							}
							//页面未勾选完工模具时,过滤已完工模具
							if (!form.isOverFlag()
									&& Float.parseFloat((String) ((Map) mouldProcesses.get(rs
											.getString("mouldID"))).get("totalProcess")) == 1) { // moulds
								continue;
							}
							//页面未勾选未完工模具时,过滤未完工模具
							if (!form.isDoingFlag()
									&& Float.parseFloat((String) ((Map) mouldProcesses.get(rs
											.getString("mouldID"))).get("totalProcess")) < 1) { // moulds
								continue;
							}
							//隐藏已完工
							if (form.getIsHideDone() && rs.getString("state").equals("over")) { // hide
								continue;
							}
							if (!rs.getString("mouldID").equals(mouldID)) {
								mouldID = rs.getString("mouldID");
								mould = new Mould();
								moulds.add(mould);
								mould.setPartList((ArrayList) defaultList.clone());
								mould.setPurchase(puMap.get(mouldID));

								Map tempMap = (Map) mouldProcesses.get(mouldID);
								double tempProcess = Double.parseDouble((String) tempMap
										.get("totalProcess"));
								if (tempProcess > 0.99 && tempProcess < 1) {
									tempMap.put("totalProcess", "0.99");
									tempProcess = 0.99;
								}
								mould.setTotalPercent(String.valueOf((int) (tempProcess * 100))
										+ "%");
								mould.setProcessPercent(mouldProcesses.get(mouldID));
								mould.setMouldInteriorID(rs.getString("mouldInteriorID"));
								mould.setMouldClientID(rs.getString("mouldClientID"));
								mould.setMouldID(rs.getString("mouldID"));
								mould.setMouldName(rs.getString("mouldName"));
								mould.setMouldClass(rs.getString("MClass"));
								mould.setMouldBasicClass(rs.getString("MBClass"));
								mould.setMouldWorkingState(rs.getString("mouldWorkingState"));
								mould.setScheduleTerm(rs.getDate("scheduleTerm"));
								mould.setLeadTime(rs.getDate("leadTime"));

								mould.setDeferDay(rs.getInt("deferDay"));
								mould.setIsExterior(rs.getString("canExterior"));
								mould.setInstoreTime(rs.getTimestamp("maxStoreTime"));
								mould.setCadList(taskList.get(mouldID));
								mould.setCadProcess(taskProcess.get(mouldID) == null ? "0"
										: taskProcess.get(mouldID));
								mould.setCamList(cncList.get(mouldID));
								mould.setCadProcess(cncProcess.get(mouldID) == null ? "0"
										: cncProcess.get(mouldID));
								mould.setProductID(rs.getString("produID"));
								mould.setProductInteriorID(rs.getString("productInteriorID"));
								mould.setProductClientID(rs.getString("productClientID"));
								mould.setProductName(rs.getString("productName"));
								mould.setProjectID(rs.getString("projID"));
								mould.setProjectClientID(rs.getString("projectClientID"));
								mould.setProjectInteriorID(rs.getString("projectInteriorID"));
								mould.setProjectName(rs.getString("projectName"));

								mouldMap.put(mould.getMouldID(), mould);
							}
							if (!rs.getString("partID").equals(partID)) {
								boolean partOver = true;
								// 此处判断上一道工件是否延期
								if (part != null && part.getOperationList() != null) {
									// 判断工件是否已经完工
									for (int i = 0; i < part.getOperationList().size(); i++) {
										Operation op = part.getOperationList().get(i);
										if (!op.getState().equals("over")) {
											partOver = false;
											break;
										}
									}
									if (!partOver) {
										// 如果工件未完工
										Mould tempMould = mouldMap.get(part.getMouldId());
										Timestamp schedule = Timestamp.valueOf(format
												.format(tempMould.getScheduleTerm())
												+ " 23:59:59");
										// 工件最后完成的一道工序计划完工时间不为空且晚于预估交期
										part.setIsDeferred(isDelayedPart(schedule, part
												.getOperationOrderList()));

									}
								}

								partID = rs.getString("partID");
								part = new Part();
								overList = new ArrayList();
								workingList = new ArrayList();
								pauseList = new ArrayList();
								undoList = new ArrayList();
								mould.getPartList().add(part);
								part.setShowTime(config.getShowTime());
								part.setMouldId(rs.getString("mouldID"));
								part.setOperationList((ArrayList) defaultList.clone());
								part.setOperationOrderList(new ArrayList());
								part.setPartId(rs.getString("partID"));
								part.setPartMonitorID(rs.getInt("partMonitorID"));
								part.setPartClassName(rs.getString("partClassName"));
								part.setProcessPercentMap((Map) partProcesses.get(rs
										.getString("partID")));
								Map tempMap = (Map) partProcesses.get(partID);
								double tempProcess = Double.parseDouble((String) tempMap
										.get("totalProcess"));
								if (tempProcess > 0.99 && tempProcess < 1) {
									tempMap.put("totalProcess", "0.99");
									tempProcess = 0.99;
								}
								part
										.setPartProcess(String.valueOf((int) (tempProcess * 100))
												+ "%");
								part.setPartName(rs.getString("partName"));
								part.setVerifyID(rs.getString("verifyID"));
								part.setWorkId(rs.getString("workID"));
								part.setMaterialName(rs.getString("materialName"));
								part.setPartOrderId(rs.getInt("partOrderId"));
								if (rs.getString("machiningAttribute") == null) { // 增加对工件加工属性的空指针判断，如果为null，则默认为in
									// hjp
									// 2009-4-13
									part.setMachiningAttribute(rs.getString("in"));
								} else {
									part.setMachiningAttribute(rs.getString("machiningAttribute"));
								}
								part.setPartNums(rs.getInt("partNums"));
								part.setStandardNumber(rs.getString("standardNumber"));
								part.setSpecifications(rs.getString("specifications"));
								part.setState(rs.getString("MSTATE"));
								part.setDesigner(rs.getString("designer"));
								part.setPartType(rs.getInt("partType"));
								part.setRemark(rs.getString("remark"));
								part.setDesignDate(rs.getTimestamp("designDate"));
								TransferPartMonitor transferPartMonitor = new TransferPartMonitor(); // kukuxia
								// 2007-07-09
								// 取出该道工序的最新流转纪录
								transferPartMonitor = (TransferPartMonitor) transferPartMonitorMapByPart
										.get(rs.getString("partMonitorID"));
								if (transferPartMonitor != null) {
									part.setTransferCurrentDeptName(transferPartMonitor
											.getCurrentDeptName());
									part.setTransferDeptName(transferPartMonitor
											.getTransferDeptName());
									part.setTransferOperatorName(transferPartMonitor
											.getOperatorName());
									part.setTransferTime(transferPartMonitor.getTransferTime());
									part.setTransferState(transferPartMonitor.getTransferState());
								}
							}

							operation = new Operation();
							operationMap.put(rs.getLong("ID"), operation);
							operation.setOperationId(rs.getLong("ID"));
							operation.setPreList(preMap.get(rs.getLong("ID")));
							operation.setOperationName(rs.getString("operationName"));
							operation.setOperationContent(rs.getString("operationContent")
									.replaceAll("\\r\\n", "<br>"));
							operation.setPlanHours(rs.getString("processingHoursQuota"));
							// operation.setActualWorkHour(rs.getString("processingHoursActual"));
							operation
									.setActualHours((monitors.containsKey(rs.getString("ID")) ? (String) monitors
											.get(rs.getString("ID"))
											: rs.getString("processingHoursActual")));
							operation.setIsExterior(rs.getString("planExterior"));

							operation.setOperationType(rs.getString("type"));
							if (rs.getString("isEligible") != null) {
								operation.setIsEligible(rs.getInt("isEligible"));
							}
							if (rs.getString("isDealed") != null) {
								operation.setIsDealed(rs.getInt("isDealed"));
							}
							operation.setOperator(getOperators(rs.getString("actualOperatorID"),
									humans));
							operation.setPlanStartTime(rs.getTimestamp("planStartTime"));
							operation.setPlanStopTime(rs.getTimestamp("planStopTime"));
							operation.setActualStartTime(rs.getTimestamp("actualStartTime"));
							operation.setActualStopTime(rs.getTimestamp("actualStopTime"));
							operation.setPlanResource(rs.getString("planResource"));
							operation.setActualResource(rs.getString("actualResource"));
							operation.setHasFile(rs.getString("fileName") == null ? false : true); // 该工序是否有上传的文档
							operation.setState(rs.getString("state"));

							operation.setOperationOrderId(rs.getInt("operationOrderId"));
							operation.setPartOrderId(rs.getInt("partOrderId"));
							if (operation.getState().equals("over")) {
								operation.setDefer(checkDefer(operation.getPlanStopTime(),
										operation.getActualStopTime()));
							}

							if (!rs.getString("state").equals("over")) {
								mould.setPlanFinishTime(DateUtils.max(mould.getPlanFinishTime(), rs
										.getTimestamp("planStopTime")));
							}
							// }
							TransferPartMonitor transferPartMonitor = new TransferPartMonitor(); // kukuxia
							// 2007-07-09
							// 取出该道工序的最新流转纪录
							transferPartMonitor = (TransferPartMonitor) transferPartMonitorMapByOperation
									.get(rs.getString("ID"));
							if (transferPartMonitor != null) {
								operation.setTransferCurrentDeptName(transferPartMonitor
										.getCurrentDeptName());
								operation.setTransferDeptName(transferPartMonitor
										.getTransferDeptName());
								operation.setTransferOperatorName(transferPartMonitor
										.getOperatorName());
								operation.setTransferTime(transferPartMonitor.getTransferTime());
								operation.setTransferState(transferPartMonitor.getTransferState());
							}

							if (rs.getString("state").equals("waiting")) {
								undoList.add(operation);
							} else if (rs.getString("state").equals("working")) {
								workingList.add(operation);
							} else if (rs.getString("state").equals("pause")) {
								pauseList.add(operation);
							} else if (rs.getString("state").equals("over")) {
								overList.add(operation);
							}
							part.getOperationOrderList().add(operation);

							if (isByOperationOrderID) { // 是否根据零件工艺设计中的工艺顺序
								part.getOperationList().add(operation);
								part.setOperationNum(part.getOperationList().size());
								maxOperationNums = Math.max(maxOperationNums, part
										.getOperationList().size());
							} else {
								part.getOperationList().clear();
								part.getOperationList().addAll(undoList);
								part.getOperationList().addAll(workingList);
								part.getOperationList().addAll(pauseList);
								part.getOperationList().addAll(overList);
								part.setOperationNum(part.getOperationList().size());
								maxOperationNums = Math.max(maxOperationNums, part
										.getOperationList().size());
							}
						}
						log.info("@#$装配工序信息历时： " + (System.currentTimeMillis() - start));
						return new Integer(maxOperationNums + 1);
					}
				});

		if (moulds.size() > 0) {
			Mould mould = (Mould) moulds.get(moulds.size() - 1);
			if (mould.getPartList() != null && mould.getPartList().size() > 0) {
				Part part = (Part) mould.getPartList().get(mould.getPartList().size() - 1);
				if (part != null && part.getOperationList() != null) {
					boolean partOver = true;
					// 判断工件是否已经完工
					for (int i = 0; i < part.getOperationList().size(); i++) {
						Operation op = (Operation) part.getOperationList().get(i);
						if (!op.getState().equals("over")) {
							partOver = false;
							break;
						}
					}
					if (!partOver) {
						// 如果工件未完工
						Timestamp schedule = Timestamp.valueOf(format.format(mould
								.getScheduleTerm())
								+ " 23:59:59");
						// 工件最后完成的一道工序计划完工时间不为空且晚于预估交期
						part.setIsDeferred(isDelayedPart(schedule, part.getOperationOrderList()));
					}
				}

			}
		}
		/*
		 * if (partOrder.equals("partName")) { // 按照工件名称排序 maple 2007-09-18 add for (int i = 0; i <
		 * moulds.size(); i++) { Mould mouldBean = new Mould(); mouldBean = (Mould) moulds.get(i);
		 * Collections.sort(mouldBean.getPartList(), new java.util.Comparator() { public int
		 * compare(Object obj1, Object obj2) { return ((Part) obj1).getPartName().compareTo(((Part)
		 * obj2).getPartName()); } }); } }
		 */
		// sam 3.5.1 2007-10-23 加入延期天数比较的基准时间
		if (delayBenchmark != null && delayBenchmark.equals("plan")) {
			for (int i = 0; i < moulds.size(); i++) {
				Mould mouldBean = (Mould) moulds.get(i);
				if (mouldBean.getPlanFinishTime() != null && mouldBean.getScheduleTerm() != null
						&& mouldBean.getPlanFinishTime().after(mouldBean.getScheduleTerm())) {
					// 预估交期不为空则按照预估交期来计算
					mouldBean
							.setDeferDay((int) ((mouldBean.getPlanFinishTime().getTime() - mouldBean
									.getScheduleTerm().getTime()) / (3600 * 24 * 1000)));
				} else {
					mouldBean.setDeferDay(0);
				}
			}
		}
		List<Field> mouldFieldList = queryMouldFieldList("MouldScheduleInfo");
		List<Field> partFieldList = queryMouldFieldList("PartScheduleInfo");
		for (Mould mould : moulds) {
			mould.setMouldFieldSize(mouldFieldList.size());
			mould.setPartFieldSize(partFieldList.size());
			List<Field> list = new ArrayList<Field>();
			for (Field field : mouldFieldList) {
				Field field2 = new Field();
				BeanUtils.copyProperties(field2, field);
				Object object = BeanUtils.getProperties(field2.getFieldName(), mould);
				if (object != null) {
					if (object instanceof Date) {
						field2.setFieldValue(DateUtils.formatYMD((Date) object));
					} else {
						field2.setFieldValue(object.toString());
					}
				} else {
					field2.setFieldValue("");
				}
				list.add(field2);
			}
			mould.setFieldList(list);
			int max = mould.getMaxOperationNums();
			List<Part> partList = mould.getPartList();
			for (int p = 0; p < partList.size(); p++) {
				Part part = partList.get(p);
				part.setMaxOperationNums(max);
				part.setPartOrderId(p + 1);
				if (StringUtils.isNotEmpty(form.getPartMonitorID())
						&& form.getPartMonitorID().equals(String.valueOf(part.getPartMonitorID()))) {
					mould.setPartPosition(p);
				}
				for (int t = 0; part.getOperationList() != null
						&& t < part.getOperationList().size(); t++) {
					part.getOperationList().get(t).setOperationOrderId(t + 1);

				}
				List<Field> list2 = new ArrayList<Field>();
				for (Field field : partFieldList) {
					Field field2 = new Field();
					BeanUtils.copyProperties(field2, field);
					Object object = BeanUtils.getProperties(field2.getFieldName(), part);
					if (object != null) {
						if (object instanceof Date) {
							field2.setFieldValue(DateUtils.formatYMD((Date) object));
						} else {
							field2.setFieldValue(object.toString());
						}
					} else {
						field2.setFieldValue("");
					}
					list2.add(field2);
				}
				part.setFieldList(list2);
			}
			mould.setMaxOperationNums(maxOperationNums);
		}

		returnMap.put("mouldList", moulds);
		returnMap.put("maxOperationNums", maxOperationNums);
		return moulds;
	}

	public Map<String, Map> queryMouldProcessPercent(Form form) {
		StringBuffer sql = new StringBuffer(1024);
		sql
				.append(" SELECT mouldID,operationType,SUM(quotaHoursSum) AS quotaHours,SUM(actualHoursSum) AS actualHours, "
						+ " CASE SUM(quotaHoursSum) WHEN 0 THEN 0 ELSE SUM(actualHoursSum)/SUM(quotaHoursSum) END as operationTypePercent "
						+ " FROM ( "
						+ " SELECT MPT.mouldID, isNull(OperationName.subType,0) AS operationType, "
						+ " CAST(MPT.processingHoursQuota AS FLOAT) AS quotaHoursSum, "
						+ " CASE WHEN MPT.state = 'waiting' THEN 0 "
						+ "      WHEN MPT.state = 'over' THEN MPT.processingHoursQuota "
						+ "      WHEN MPT.state <> 'over' AND MPT.state <> 'waiting' "
						+ "           AND MPT.processingHoursActual > CAST(MPT.processingHoursQuota AS FLOAT)"
						+ "        THEN CAST(MPT.processingHoursQuota AS FLOAT) * 0.8 "
						+ "      ELSE MPT.processingHoursActual "
						+ " END AS actualHoursSum "
						+ " FROM MouldPartTechnics MPT WITH (NOLOCK) "
						+ " INNER JOIN OperationName WITH (NOLOCK) ON OperationName.operationNameID = MPT.operationNameID "
						+ " INNER JOIN Mould WITH (NOLOCK) ON Mould.mouldID = MPT.mouldID "
						+ " WHERE (OperationName.mark = 'applying') AND (Mould.mark = 'applying') AND (MPT.mark = 'applying') "
						+ " AND Mould.mouldName LIKE ? AND Mould.mouldInteriorID LIKE ? ");
		if (form.getMouldClassID() != null && !form.getMouldClassID().equals("mo_01")) {
			sql.append(" AND Mould.mouldClassID LIKE  '" + form.getMouldClassID() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldTextId())) {
			sql.append(" AND MPT.mouldID LIKE '" + form.getMouldTextId() + "%' "
					+ " and Mould.mouldID LIKE '" + form.getMouldTextId() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldInteriorID())) {
			sql.append(" AND Mould.mouldInteriorID LIKE  '%" + form.getMouldInteriorID() + "%' ");
		}
		sql.append(" ) TEMP " + " GROUP BY mouldID, operationType ORDER BY mouldID ");
		final Map mouldsMap = new HashMap();
		jdbcTemplate.query(sql.toString(), new Object[] {
				"%" + StringUtils.formatNull(form.getMouldName()) + "%",
				"%" + StringUtils.formatNull(form.getMouldInteriorIDName()) + "%" },
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException {
						String mouldID = null;
						float[] defaultArray = new float[2];
						Map operationHours = null;
						float[] temp = null;
						while (rs.next()) {
							if (!rs.getString("mouldID").equals(mouldID)) {
								if (mouldID != null) {
									if (temp[1] == 0) {
										((Map) mouldsMap.get(mouldID)).put("totalProcess", "0");
									} else {
										((Map) mouldsMap.get(mouldID)).put("totalProcess", String
												.valueOf(temp[0] / temp[1]));
									}
								}
								mouldID = rs.getString("mouldID");
								operationHours = new HashMap();
								temp = (float[]) defaultArray.clone();
								mouldsMap.put(mouldID, operationHours);
								operationHours.put("totalProcess", "0");
							}
							temp[0] += rs.getFloat("actualHours");
							temp[1] += rs.getFloat("quotaHours");
							operationHours.put(rs.getString("operationType"), rs
									.getString("operationTypePercent"));
							if (rs.isLast()) {
								if (temp[1] == 0) {
									((Map) mouldsMap.get(mouldID)).put("totalProcess", "0");
								} else {
									((Map) mouldsMap.get(mouldID)).put("totalProcess", String
											.valueOf(temp[0] / temp[1]));
								}
							}
						}
						return null;
					}
				});
		return mouldsMap;
	}

	/**
	 * 查询工件完成百分比
	 * @param form
	 * @return
	 */
	public Map queryPartProcessPercent(Form form) {
		StringBuffer sql = new StringBuffer(1024);
		sql
				.append(" SELECT partID,operationType,SUM(quotaHoursSum) AS quotaHours,SUM(actualHoursSum) AS actualHours, "
						+ " CASE SUM(quotaHoursSum) WHEN 0 THEN 0 ELSE SUM(actualHoursSum)/SUM(quotaHoursSum) END as operationTypePercent "
						+ " FROM ( "
						+ " SELECT MPT.partID, isNull(OperationName.subType,0) AS operationType, "
						+ " CAST(MPT.processingHoursQuota AS FLOAT) AS quotaHoursSum, "
						+ " CASE WHEN MPT.state = 'waiting' THEN 0 "
						+ "      WHEN MPT.state = 'over' THEN MPT.processingHoursQuota "
						+ "      WHEN MPT.state <> 'over' AND MPT.state <> 'waiting' "
						+ "           AND MPT.processingHoursActual > CAST(MPT.processingHoursQuota AS FLOAT)"
						+ "        THEN CAST(MPT.processingHoursQuota AS FLOAT) * 0.8 "
						+ "      ELSE MPT.processingHoursActual "
						+ " END AS actualHoursSum "
						+ " FROM MouldPartTechnics MPT WITH (NOLOCK) "
						+ " INNER JOIN OperationName WITH (NOLOCK) ON OperationName.operationNameID = MPT.operationNameID "
						+ " INNER JOIN MouldPart WITH (NOLOCK) ON MouldPart.partID = MPT.partID "
						+ " INNER JOIN Mould WITH (NOLOCK) ON Mould.mouldID=MouldPart.mouldID "
						+ " WHERE (MouldPart.mark='applying') AND (MPT.mark = 'applying') "
						+ " AND Mould.mouldName LIKE ? AND Mould.mouldInteriorID LIKE ? ");
		if (form.getMouldClassID() != null && !form.getMouldClassID().equals("mo_01")) {
			sql.append(" AND Mould.mouldClassID LIKE  '" + form.getMouldClassID() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldTextId())) {
			sql.append(" AND Mould.mouldID LIKE '" + form.getMouldTextId()
					+ "%' AND MPT.mouldID LIKE '" + form.getMouldTextId() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldInteriorID())) {
			sql.append(" AND Mould.mouldInteriorID LIKE  '%" + form.getMouldInteriorID() + "%' ");
		}

		sql.append(" ) TEMP " + " GROUP BY partID,operationType ORDER BY partID ");
		final Map mouldPartsMap = new HashMap();
		jdbcTemplate.query(sql.toString(), new Object[] {
				"%" + StringUtils.formatNull(form.getMouldName()) + "%",
				"%" + StringUtils.formatNull(form.getMouldInteriorIDName()) + "%" },
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException {
						String partID = null;
						float[] defaultArray = new float[2];
						Map operationHours = null;
						float[] temp = null;
						while (rs.next()) {
							if (!rs.getString("partID").equals(partID)) {
								if (partID != null) {
									if (temp[1] == 0) {
										((Map) mouldPartsMap.get(partID)).put("totalProcess", "0");
									} else {
										((Map) mouldPartsMap.get(partID)).put("totalProcess",
												String.valueOf(temp[0] / temp[1]));
									}
								}
								partID = rs.getString("partID");
								operationHours = new HashMap();
								temp = (float[]) defaultArray.clone();
								mouldPartsMap.put(partID, operationHours);
								operationHours.put("totalProcess", "0");
							}
							temp[0] += rs.getFloat("actualHours");
							temp[1] += rs.getFloat("quotaHours");
							operationHours.put(rs.getString("operationType"), rs
									.getString("operationTypePercent"));
							if (rs.isLast()) {
								if (temp[1] == 0) {
									((Map) mouldPartsMap.get(partID)).put("totalProcess", "0");
								} else {
									((Map) mouldPartsMap.get(partID)).put("totalProcess", String
											.valueOf(temp[0] / temp[1]));
								}
							}
						}
						return null;
					}
				});
		return mouldPartsMap;
	}

	public Map<Long, List<Operation>> queryPreOperation(Form form) {
		StringBuffer sql = new StringBuffer(512);
		sql.append("SELECT TechnicsRestrict.preOperationID, TechnicsRestrict.operationID,  ");
		sql.append("MouldPartTechnics_1.operationOrderID, MouldPart.partOrderID  ");
		sql.append("FROM TechnicsRestrict INNER JOIN MouldPartTechnics ON  ");
		sql.append("TechnicsRestrict.operationID = MouldPartTechnics.ID INNER JOIN  ");
		sql.append("Mould ON MouldPartTechnics.mouldID = Mould.mouldID INNER JOIN  ");
		sql.append("MouldPartTechnics MouldPartTechnics_1 ON  ");
		sql.append("TechnicsRestrict.preOperationID = MouldPartTechnics_1.ID INNER JOIN  ");
		sql.append("MouldPart ON MouldPartTechnics_1.partID = MouldPart.partID ");

		sql.append(" WHERE (MouldPart.mark='applying') AND (MouldPartTechnics.mark = 'applying') ");
		sql.append(" AND Mould.mouldName LIKE ? AND Mould.mouldInteriorID LIKE ? ");
		if (form.getMouldClassID() != null && !form.getMouldClassID().equals("mo_01")) {
			sql.append(" AND Mould.mouldClassID LIKE  '" + form.getMouldClassID() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldTextId())) {
			sql.append(" AND Mould.mouldID LIKE '" + form.getMouldTextId() + "%' ");
		}
		if (form.getMouldWorkingState() != 0) {
			sql.append(" and Mould.mouldWorkingState = " + form.getMouldWorkingState());
		}
		if (StringUtils.isNotEmpty(form.getMouldInteriorID())) {
			sql.append(" AND Mould.mouldInteriorID LIKE  '%" + form.getMouldInteriorID() + "%' ");
		}
		if (form.getIsHideDone()) {
			sql.append(" AND MouldPartTechnics_1.state <> 'over' ");
		}

		sql.append(" ORDER BY TechnicsRestrict.operationID");
		final Map<Long, List<Operation>> map = new HashMap();
		jdbcTemplate.query(sql.toString(), new Object[] {
				"%" + StringUtils.formatNull(form.getMouldName()) + "%",
				"%" + StringUtils.formatNull(form.getMouldInteriorIDName()) + "%" },
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException {
						while (rs.next()) {
							Operation operation = new Operation();
							operation.setOperationId(rs.getLong("preOperationID"));
							operation.setOperationOrderId(rs.getInt("operationOrderId"));
							operation.setPartOrderId(rs.getInt("partOrderId"));
							if (map.get(rs.getLong("operationID")) == null) {
								List<Operation> list = new ArrayList<Operation>();
								list.add(operation);
								map.put(rs.getLong("operationID"), list);
							} else {
								List<Operation> list = map.get(rs.getLong("operationID"));
								list.add(operation);
							}
						}
						return null;
					}
				});
		return map;
	}

	public Map queryMonitors(Form form) {
		StringBuffer sql = new StringBuffer(512);
		sql.append(" SELECT  operationID,monitorTime, "
				+ " datediff(ss,monitorTime,getDate())*len(operatorid)/6 AS diffHuman "
				+ " FROM MONITOR  mo WITH (NOLOCK) "
				+ " inner join MouldPart mp WITH (NOLOCK) on mo.partmonitorid=mp.id "
				+ " inner join Mould M WITH (NOLOCK) on mp.mouldID=M.mouldID "
				+ " WHERE M.mark='applying' and mp.mark='applying' "
				+ " AND M.mouldName LIKE ? AND M.mouldInteriorID LIKE ? ");
		if (form.getMouldClassID() != null && !form.getMouldClassID().equals("mo_01")) {
			sql.append(" AND M.mouldClassID LIKE  '" + form.getMouldClassID() + "%' ");
		}
		if (StringUtils.isNotEmpty(form.getMouldTextId())) {
			sql.append(" AND M.mouldID LIKE '" + form.getMouldTextId() + "%' ");
		}
		if (form.getMouldWorkingState() != 0) {
			sql.append(" and mouldWorkingState = " + form.getMouldWorkingState());
		}
		if (StringUtils.isNotEmpty(form.getMouldInteriorID())) {
			sql.append(" AND M.mouldInteriorID LIKE  '%" + form.getMouldInteriorID() + "%' ");
		}

		sql.append(" ORDER BY operationID,monitorTime ");
		final Map monitors = new HashMap();
		jdbcTemplate.query(sql.toString(), new Object[] {
				"%" + StringUtils.formatNull(form.getMouldName()) + "%",
				"%" + StringUtils.formatNull(form.getMouldInteriorIDName()) + "%" },
				new ResultSetExtractor() {
					public Object extractData(ResultSet rs) throws SQLException {
						String operationID = null;
						float actualhours = 0F;
						int flag = 0, POSITIVE = 1, NEGITIVE = -1;
						while (rs.next()) {
							if (!rs.getString("operationID").equals(operationID)) {
								if (operationID != null) {
									monitors.put(operationID, actualhours / 3600 + "");
									actualhours = flag = 0;
								}
								operationID = rs.getString("operationID");
							}
							actualhours += ((flag++ & 1) == 0 ? POSITIVE : NEGITIVE)
									* rs.getLong("diffHuman");
						}
						if (!monitors.containsKey(operationID)) {
							monitors.put(operationID, actualhours / 3600 + "");
						}
						return null;
					}
				});
		return monitors;
	}

	public Map<String, Purchase> queryPurchase(Form mouldForm) {
		// 查询设计任务
		StringBuffer designSql = new StringBuffer(512);
		designSql
				.append("SELECT mouldIDBelong, SUM(undone) AS undone, SUM(delay) AS delay, SUM(done) as done, ");
		designSql.append("max(plantime) as planfinishtime,min(plantime) as planstarttime ");
		designSql
				.append("FROM (SELECT num.mouldIDBelong,plantime, CASE WHEN plantime < getdate() AND  ");
		designSql.append("lastupdatetime IS NULL THEN total ELSE 0 END AS undone, ");
		designSql.append("CASE WHEN lastupdatetime IS NOT NULL AND ");
		designSql.append("plantime < lastupdatetime THEN total ELSE 0 END AS [delay], ");
		designSql.append("CASE WHEN lastupdatetime IS NOT NULL AND ");
		designSql.append("plantime >= lastupdatetime THEN total ELSE 0 END AS done ");
		designSql.append("FROM (SELECT SUM(OrderBillInfo.orderNumber) AS total, ");
		designSql.append("OrderBillInfo.mouldIDBelong, OrderBillInfo.orderID ");
		designSql.append("FROM OrderBillInfo INNER JOIN ");
		designSql.append("Mould ON OrderBillInfo.mouldIDBelong = Mould.mouldID INNER JOIN ");
		designSql.append("OrderBill ON OrderBillInfo.orderID = OrderBill.orderID ");
		designSql.append(" where 1=1 ");
		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			designSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID()
					+ "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			designSql.append(" AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		if (StringUtils.isNotEmpty(mouldForm.getMouldInteriorID())) {
			designSql.append(" AND Mould.mouldInteriorID LIKE  '%" + mouldForm.getMouldInteriorID()
					+ "%' ");
		}

		designSql.append("GROUP BY OrderBillInfo.mouldIDBelong, OrderBillInfo.orderID) ");
		designSql.append("num LEFT OUTER JOIN ");
		designSql.append("OrderBill ON num.orderID = OrderBill.orderID) g ");
		designSql.append("GROUP BY mouldIDBelong");
		final Map<String, Purchase> returnMap = new HashMap<String, Purchase>();
		jdbcTemplate.query(designSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				while (rs.next()) {
					Purchase purchase = new Purchase();
					purchase.setDelay(rs.getInt("delay"));
					purchase.setDone(rs.getInt("done"));
					purchase.setUndone(rs.getInt("undone"));
					purchase.setPlanStartTime(rs.getDate("planStartTime"));
					purchase.setPlanFinishTime(rs.getDate("planFinishTime"));
					returnMap.put(rs.getString("mouldIDBelong"), purchase);
				}
				return null;
			}
		});
		return returnMap;
	}

	public Map<String, String> queryDesignProcess(Form mouldForm) {
		// 查询设计任务
		StringBuffer designSql = new StringBuffer(512);
		designSql.append("SELECT SUM(designTask.planHours*designTask.taskProgress/100) AS finish,");
		designSql.append("SUM(designTask.planHours) AS total, designTask.mouldID ");
		designSql.append("FROM designTask INNER JOIN Mould ON designTask.mouldID = Mould.mouldID ");
		designSql.append(" where 1=1 ");
		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			designSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID()
					+ "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			designSql.append(" AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		if (StringUtils.isNotEmpty(mouldForm.getMouldInteriorID())) {
			designSql.append(" AND Mould.mouldInteriorID LIKE  '%" + mouldForm.getMouldInteriorID()
					+ "%' ");
		}
		designSql.append(" GROUP BY designTask.mouldID");
		final Map<String, String> returnMap = new HashMap<String, String>();
		jdbcTemplate.query(designSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				while (rs.next()) {
					if (rs.getFloat("total") == 0) {
						returnMap.put(rs.getString("mouldID"), "0");
					} else {
						returnMap.put(rs.getString("mouldID"), String.valueOf((int) (100 * rs
								.getFloat("finish") / rs.getFloat("total"))));
					}

				}
				return null;
			}
		});
		return returnMap;
	}

	public Map<String, String> queryCNCProcess(Form mouldForm) {
		// 查询设计任务
		StringBuffer designSql = new StringBuffer(512);
		designSql.append("SELECT CNCTask.mouldID, ");
		designSql
				.append("SUM(CASE WHEN CNCTask.CNCState = 'waiting' THEN 0 WHEN CNCTask.CNCState = 'taskFinish' ");
		designSql.append("THEN planhours WHEN CNCTask.CNCState <> 'waiting' AND ");
		designSql.append("CNCTask.CNCState <> 'taskFinish' AND ");
		designSql.append("CNCTask.planHours > CNCTask.actualHours THEN (CNCTask.actualHours) ");
		designSql
				.append("ELSE CNCTask.planHours * 0.8 END) AS finish, SUM(CNCTask.planHours) AS total ");
		designSql.append("FROM CNCTask INNER JOIN Mould ON CNCTask.mouldID = Mould.mouldID ");
		designSql.append(" where 1=1 ");
		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			designSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID()
					+ "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			designSql.append(" AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		if (StringUtils.isNotEmpty(mouldForm.getMouldInteriorID())) {
			designSql.append(" AND Mould.mouldInteriorID LIKE  '%" + mouldForm.getMouldInteriorID()
					+ "%' ");
		}
		designSql.append(" GROUP BY CNCTask.mouldID");
		final Map<String, String> returnMap = new HashMap<String, String>();
		jdbcTemplate.query(designSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				while (rs.next()) {
					if (rs.getFloat("total") == 0) {
						returnMap.put(rs.getString("mouldID"), "0");
					} else {
						returnMap.put(rs.getString("mouldID"), String.valueOf((int) (100 * rs
								.getFloat("finish") / rs.getFloat("total"))));
					}

				}
				return null;
			}
		});
		return returnMap;
	}

	public Map<String, List<DesignProcess>> queryDesignTask(Form mouldForm) {
		// 查询设计任务
		StringBuffer designSql = new StringBuffer(512);
		designSql
				.append("SELECT designTask.mouldID DMID, taskName, taskID,designState FROM designTask ");
		designSql.append("inner join Mould on designTask.mouldID=Mould.mouldID");
		designSql.append(" where 1=1 ");

		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			designSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID()
					+ "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			designSql.append(" AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		designSql.append(" ORDER BY designTask.mouldID, taskID");
		final Map<String, List<DesignProcess>> returnMap = new HashMap<String, List<DesignProcess>>();
		jdbcTemplate.query(designSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				String preMouldID = "";
				ArrayList taskList = null;
				while (rs.next()) {
					DesignProcess designProcessBean = new DesignProcess();
					designProcessBean.setMouldID(rs.getString("DMID"));
					designProcessBean.setTaskID(rs.getInt("taskID"));
					designProcessBean.setTaskName(rs.getString("taskName"));
					designProcessBean.setTaskType("designTask");
					if (rs.getInt("designState") == 0 || rs.getInt("designState") == 1) {
						designProcessBean.setState("waiting");
					} else if (rs.getInt("designState") == 3 || rs.getInt("designState") == 4) {
						designProcessBean.setState("over");
					} else {
						designProcessBean.setState("working");
					}
					if (rs.getString("DMID").equals(preMouldID)) {
						taskList.add(designProcessBean);
					} else {
						taskList = new ArrayList();
						taskList.add(designProcessBean);
						returnMap.put(rs.getString("DMID"), taskList);
						preMouldID = rs.getString("DMID");
					}
				}
				return null;
			}
		});
		return returnMap;
	}

	public Map<String, List<DesignProcess>> queryCNCTask(Form mouldForm) {
		// 查询编程任务
		StringBuffer cncSql = new StringBuffer(512);
		cncSql.append("SELECT CNCTask.mouldID CMID, taskName, CNCID,CNCState  FROM CNCTask");
		cncSql.append(" inner join Mould on CNCTask.mouldID=Mould.mouldID");
		cncSql.append(" where 1=1 ");

		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			cncSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID() + "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			cncSql.append("  AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		cncSql.append(" ORDER BY Mould.mouldID, CNCID");
		final Map<String, List<DesignProcess>> returnMap = new HashMap<String, List<DesignProcess>>();
		jdbcTemplate.query(cncSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				String preMouldID = "";
				ArrayList taskList = null;
				while (rs.next()) {
					DesignProcess designProcessBean = new DesignProcess();
					designProcessBean.setMouldID(rs.getString("CMID"));
					designProcessBean.setTaskID(rs.getInt("CNCID"));
					designProcessBean.setTaskName(rs.getString("taskName"));
					designProcessBean.setTaskType("cncTask");
					designProcessBean.setState(rs.getString("cncState"));
					if (rs.getString("CMID").equals(preMouldID)) {
						taskList.add(designProcessBean);
					} else {
						taskList = new ArrayList();
						taskList.add(designProcessBean);
						returnMap.put(rs.getString("CMID"), taskList);
						preMouldID = rs.getString("CMID");
					}
				}
				return null;
			}
		});

		return returnMap;
	}

	public Map<String, List<DesignProcess>> queryTaskProgress(Form mouldForm) {
		// 查询设计任务
		StringBuffer designSql = new StringBuffer(512);
		designSql.append("SELECT designTask.mouldID DMID, taskName, taskID FROM designTask ");
		designSql.append("inner join Mould on designTask.mouldID=Mould.mouldID");
		designSql.append(" where 1=1 ");

		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			designSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID()
					+ "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			designSql.append(" AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		designSql.append(" ORDER BY designTask.mouldID, taskID");
		// 查询编程任务
		StringBuffer cncSql = new StringBuffer(512);
		cncSql.append("SELECT CNCTask.mouldID CMID, taskName, CNCID FROM CNCTask");
		cncSql.append(" inner join Mould on CNCTask.mouldID=Mould.mouldID");
		cncSql.append(" where 1=1 ");

		if (mouldForm.getMouldClassID() != null && !mouldForm.getMouldClassID().equals("mo_01")) {
			cncSql.append(" AND Mould.mouldClassID LIKE  '" + mouldForm.getMouldClassID() + "%' ");
		}
		if (mouldForm.getMouldTextId() != null && !mouldForm.getMouldTextId().equals("")) {
			cncSql.append("  AND Mould.mouldID LIKE '" + mouldForm.getMouldTextId() + "%' ");
		}
		cncSql.append(" ORDER BY Mould.mouldID, CNCID");
		final Map<String, List<DesignProcess>> returnMap = new HashMap<String, List<DesignProcess>>();
		jdbcTemplate.query(designSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				String preMouldID = "";
				ArrayList taskList = null;
				while (rs.next()) {
					if (rs.getString("DMID").equals(preMouldID)) {
						DesignProcess designProcessBean = new DesignProcess();
						designProcessBean.setMouldID(rs.getString("DMID"));
						designProcessBean.setTaskID(rs.getInt("taskID"));
						designProcessBean.setTaskName(rs.getString("taskName"));
						designProcessBean.setTaskType("designTask");
						taskList.add(designProcessBean);
					} else {
						taskList = new ArrayList();
						DesignProcess designProcessBean = new DesignProcess();
						designProcessBean.setMouldID(rs.getString("DMID"));
						designProcessBean.setTaskID(rs.getInt("taskID"));
						designProcessBean.setTaskName(rs.getString("taskName"));
						designProcessBean.setTaskType("designTask");
						taskList.add(designProcessBean);
						returnMap.put(rs.getString("DMID"), taskList);
						preMouldID = rs.getString("DMID");
					}
				}
				return null;
			}
		});

		jdbcTemplate.query(cncSql.toString(), new Object[] {}, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				String preMouldID = "";
				ArrayList taskList = null;
				while (rs.next()) {
					if (rs.getString("CMID").equals(preMouldID)) {
						DesignProcess designProcessBean = new DesignProcess();
						designProcessBean.setMouldID(rs.getString("CMID"));
						designProcessBean.setTaskID(rs.getInt("CNCID"));
						designProcessBean.setTaskName(rs.getString("taskName"));
						designProcessBean.setTaskType("cncTask");
						taskList.add(designProcessBean);
					} else {
						taskList = new ArrayList();
						DesignProcess designProcessBean = new DesignProcess();
						designProcessBean.setMouldID(rs.getString("CMID"));
						designProcessBean.setTaskID(rs.getInt("CNCID"));
						designProcessBean.setTaskName(rs.getString("taskName"));
						designProcessBean.setTaskType("cncTask");
						if (returnMap.get(rs.getString("CMID")) != null) {
							taskList = (ArrayList) returnMap.get(rs.getString("CMID"));
							taskList.add(designProcessBean);
						} else {
							taskList = new ArrayList();
							taskList.add(designProcessBean);
							returnMap.put(rs.getString("CMID"), taskList);
						}
						preMouldID = rs.getString("CMID");
					}
				}
				return null;
			}
		});

		return returnMap;
	}

	public ArrayList queryTransferPartMonitorInfo() {
		StringBuffer sql = new StringBuffer();
		final ArrayList resultList = new ArrayList();
		final HashMap transferPartMonitorMapByOperation = new HashMap();
		final HashMap transferPartMonitorMapByPart = new HashMap();
		sql
				.append("SELECT dbo.t_JGGL_TransferPartMonitor.*, dbo.Human.name, Department_1.deptName AS currentDeptName, ");
		sql
				.append("dbo.Department.deptName AS transferDeptName FROM dbo.t_JGGL_TransferPartMonitor INNER JOIN ");
		sql
				.append("dbo.Human ON dbo.t_JGGL_TransferPartMonitor.transferOperator = dbo.Human.humanID INNER JOIN ");
		sql
				.append("dbo.Department ON dbo.t_JGGL_TransferPartMonitor.transferDeptID = dbo.Department.deptID INNER JOIN ");
		sql
				.append("dbo.Department Department_1 ON dbo.Human.deptID = Department_1.deptID INNER JOIN ");
		sql
				.append("(SELECT partMonitorID, MAX(transferTime) AS transferTime FROM dbo.t_JGGL_TransferPartMonitor ");
		sql
				.append("GROUP BY partMonitorID) TempTable ON TempTable.partMonitorID = dbo.t_JGGL_TransferPartMonitor.partMonitorID AND ");
		sql.append("TempTable.transferTime = dbo.t_JGGL_TransferPartMonitor.transferTime ");

		// 执行查询
		try {
			jdbcTemplate.query(sql.toString(), new ResultSetExtractor() {
				public Object extractData(ResultSet rs) throws SQLException {
					while (rs.next()) {
						TransferPartMonitor transferPartMonitor = new TransferPartMonitor();
						transferPartMonitor.setTransferID(rs.getLong("transferID"));
						transferPartMonitor.setPartMonitorID(rs.getInt("partMonitorID"));
						transferPartMonitor.setOperationID(rs.getInt("operationID"));
						transferPartMonitor.setOperatorName(rs.getString("name"));
						transferPartMonitor.setCurrentDeptName(rs.getString("currentDeptName"));
						transferPartMonitor.setTransferDeptName(rs.getString("transferDeptName"));
						transferPartMonitor.setTransferTime(rs.getTimestamp("transferTime"));
						transferPartMonitor.setTransferState(rs.getInt("transferState"));
						transferPartMonitorMapByOperation.put(rs.getString("operationID"),
								transferPartMonitor);
						transferPartMonitorMapByPart.put(rs.getString("partMonitorID"),
								transferPartMonitor);
					}
					resultList.add(transferPartMonitorMapByOperation);
					resultList.add(transferPartMonitorMapByPart);
					return resultList;
				}
			});
		} catch (Exception ex) {
			log.info("查询每道工序最新的流转记录失败", ex);
		}
		return resultList;
	}

	public List<Field> queryMouldFieldList(String tableId) {
		List<Field> list = new ArrayList<Field>();
		String sql = "SELECT FieldConfig.fieldName, FieldConfig.fieldShowName "
				+ "FROM Template INNER JOIN "
				+ "FieldConfig ON Template.templateID = FieldConfig.templateID "
				+ "WHERE (Template.tableID = :tableID) AND (FieldConfig.isShowOnList = 1) "
				+ "ORDER BY FieldConfig.orderNum";
		Map paMap = new HashMap<String, String>();
		paMap.put("tableID", tableId);
		list = query(sql, Field.class, paMap);
		return list;
	}

	public boolean checkDefer(Date planDate, Date actualDate) {
		if (planDate == null) {
			return false;
		}
		return planDate.before(actualDate);
	}

	public String getOperators(String operatorID, Map humans) {
		StringBuffer operators = new StringBuffer(50);
		String one = null;
		if (operatorID == null || operatorID.length() < 6) {
			return "";
		}
		while (operatorID.length() >= 6) {
			one = operatorID.substring(0, 6);
			operators.append(humans.get(one) + "(" + one + ")；");
			operatorID = operatorID.substring(6);
		}
		return operators.substring(0, operators.length() - 1);
	}

	public boolean isDelayedPart(Timestamp ScheduleTerm, List partList) {

		Timestamp compareToTime = null; // 工件下最晚完工的工序计划结束时间
		for (int i = 0; i < partList.size(); i++) {
			Operation bean = (Operation) partList.get(i);
			if (bean.getPlanStopTime() != null && !bean.getState().equals("over")) {
				if (compareToTime == null) {
					compareToTime = bean.getPlanStopTime();
				} else {
					if (bean.getPlanStopTime().after(compareToTime)) {
						compareToTime = null;
						compareToTime = bean.getPlanStopTime();
					}
				}
			}
		}

		if (compareToTime != null && compareToTime.after(ScheduleTerm)) {
			return true;
		} else {
			return false;
		}
	}

	public Map<String, String> queryHumans() {
		String sql = " SELECT humanMonitorID,name FROM Human ";
		final Map<String, String> humans = new HashMap<String, String>();
		jdbcTemplate.query(sql, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException {
				while (rs.next()) {
					humans.put(rs.getString("humanMonitorID"), rs.getString("name"));
				}
				return null;
			}
		});
		return humans;
	}

	public List<MouldClass> queryMouldClassList() {
		String sql = " SELECT mouldClassID, mouldClassName FROM MouldClass "
				+ "WHERE (mark = N'applying') ORDER BY mouldClassID ";
		final List<MouldClass> list = new ArrayList<MouldClass>();
		jdbcTemplate.query(sql, new ResultSetExtractor() {
			public Object extractData(ResultSet rs) throws SQLException {
				while (rs.next()) {
					MouldClass class1 = new MouldClass();
					class1.setMouldClassId(rs.getString("mouldClassID"));
					class1.setMouldClassName(rs.getString("mouldClassName"));
					list.add(class1);
				}
				return null;
			}
		});
		return list;
	}

	public List<Mould> queryMouldList() {
		String sql = "SELECT mouldID, mouldName, mouldInteriorID FROM Mould WHERE (mark = N'applying')";
		return query(sql, Mould.class);
	}
}
